package cn.jilian.common.util;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import org.apache.commons.collections.CollectionUtils;

/**
 * 简单sql语句解析
 * */
public class SqlUtil {
	
	public static class updateSqlBuilder{
		
	}
	
	public static class SingleInsertSqlBuilder{
		private StringBuilder sb;
		
		private String table;
		
		private Map<String,Object> record;
		
		
		public SingleInsertSqlBuilder(String selectSql,Map<String,Object> record){
			this.sb=new StringBuilder();
			this.table=SqlUtil.getTable(selectSql);
			this.record=record;
		}
		
		public SingleInsertSqlBuilder(String table,Map<String,Object> record,StringBuilder sb){
			this.sb=sb;
			this.table=table;
			this.record=record;
		}
		
		public void export(){
			String prefix=buildInsertSqlPrefix(this.table,record.keySet());
			String[] cols=SqlUtil.getColumns(prefix);
			String sql=buildInsertSql(prefix,cols,record);
			this.sb.append(sql).append(";\r\n");
		}
		
		public String toString(){
			export();
			return sb.toString();
		}
	}

	/**
	 * insert sql 输出
	 * */
	public static class InsertSqlBuilder{
		
		private StringBuilder sb;
		
		private String table;
		
		private List<Map<String,Object>> records;
		
		
		public InsertSqlBuilder(String selectSql,List<Map<String,Object>> records){
			this.sb=new StringBuilder();
			this.table=SqlUtil.getTable(selectSql);
			this.records=records;
		}
		
		public InsertSqlBuilder(String table,List<Map<String,Object>> records,StringBuilder sb){
			this.sb=sb;
			this.table=table;
			this.records=records;
		}
		
		public void export(){
			if(CollectionUtils.isEmpty(this.records))return;
			Map<String,Object> record=this.records.get(0);
			String prefix=buildInsertSqlPrefix(this.table,record.keySet());
			String[] cols=SqlUtil.getColumns(prefix);
			for(Map<String,Object> n:this.records){
				String sql=buildInsertSql(prefix,cols,n);
				this.sb.append(sql).append(";\r\n");
			}
		}
		
		public String toString(){
			export();
			return sb.toString();
		}
	}
	
	/**
	 * 构造单过滤条件查询语句
	 * */
	public static String getSelectAllBySingleColumnSql(String table,String column){
		return new StringBuilder("select * from ").append(table).append(" where ")
				.append(column).append("=?").toString();
	}
	/**
	 * 解析出简单select 语句的表名
	 * */
	public static String getTable(String selectSql){
		int start=selectSql.indexOf(" from ")+6;
		String str=selectSql.substring(start);
		int end=str.indexOf(" ");
		return str.substring(0, end);
	}
	
	/**
	 * 构造出insert语句前半段
	 * e.g insert into table(sid,name,code) values;
	 * */
	public static String buildInsertSqlPrefix(String table,Collection<String> keys){
		StringBuilder sb=new StringBuilder("insert into ");
		sb.append(table)
		.append("(");
		for(String c:keys){
			if(!"TIMS".equalsIgnoreCase(c))
				sb.append(c).append(",");
		}
		sb.setLength(sb.length()-1);
		sb.append(") values");
		return sb.toString();
	}
	
	/**
	 * 由查询sql和查询记录构造insert sql
	 * */
	public static String getInsertSql(String selectSql,Map<String,Object> record){
		String table=getTable(selectSql);
		String insertSqlPrefix=buildInsertSqlPrefix(table,record.keySet());
		return buildInsertSql(insertSqlPrefix,record);
	}
	

	/**
	 * 构造出insert sql
	 * */
	public static String buildInsertSql(String prefix,String[] cols,Map<String,Object> record){
		StringBuilder sb=new StringBuilder(prefix);
		sb.append("(");
		for(String key:cols){
			Object value=record.get(key);
			String v=getValuePiece(value);
			sb.append(v).append(",");
		}
		sb.setLength(sb.length()-1);
		sb.append(")");
		return sb.toString();
	}
	
	/**
	 * 构造出insert sql
	 * */
	public static String buildInsertSql(String prefix,Map<String,Object> record){
		String[] cols=getColumns(prefix);
		return buildInsertSql(prefix,cols,record);
	}
	
	private static String getValuePiece(Object value){
		if(value==null){
			return "null";
		}
		if(value instanceof String){
			String v=(String) value;
			v=v.replaceAll("'", "\\\\'");
			v=v.replaceAll("\"", "\\\\\"");
			return "'"+v+"'";
		}
		if(value instanceof Number){
			return Objects.toString(value,"0");
		}
		if(value instanceof Date){
			Date d=(Date) value;
			String v=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(d);
			return "'"+v+"'";
		}
		if(value instanceof Timestamp ){
			String v=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(value);
			return "'"+v+"'";
		}
		return "";
	}
	
	/**
	 * 由insert sql 解析出列名
	 * */
	public static String[] getColumns(String insertSql){
		String str=insertSql.substring(insertSql.indexOf("(")+1, insertSql.indexOf(")"));
		String[] cols=str.split(",");
		return cols;
	}
	public static void main(String[] args){
		String sql="select * from bt_meta where sid=1";
		System.out.println(getTable(sql));
		String insertSql="insert into bt_meta(sid,fid,name,tims)";
		String[] cols=getColumns(insertSql);
		for(String col:cols){
			System.out.println(col);
		}
	}
}
